package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Teacher;
import cn.edu.sdjzu.xg.bysj.domain.authority.Actor;
import cn.edu.sdjzu.xg.bysj.service.StudentService;
import cn.edu.sdjzu.xg.bysj.service.TeacherService;
import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.*;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class ActorDao {
    //新建ActorDao对象
    private static ActorDao actorDao = new ActorDao();
    //结果集
    private static Collection<Actor> actors;
    //构造器
    private ActorDao() { }
    //获取实例
    public static ActorDao getInstance() {
        return actorDao;
    }
    /**
     * 由于Teacher扩展了Actor，前者对象id取后者的id的值。
     * Actor表只有id一个字段，该字段自动生成。
     * 用于事务。调用本方法的Service，在调用本方法后，还会调用UserDao.add方法
     * 两次对Dao的调用要保证是同一个Connection对象，才能将它们设置在同一事务中
     *
     * @param connection 来自Service的Connection对象
     * @return 成功增加：新对象的id，失败：0
     * @throws SQLException
     */
    public int add(Connection connection) throws SQLException {
        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement preparedStatement_actor =
                connection.prepareStatement(
                        "INSERT INTO actor " +
                                " ()" +
                                " VALUES ()", Statement.RETURN_GENERATED_KEYS);//要求返回主键集合
        //执行预编译语句，用其返回值、影响的行数为赋值affectedRowNum
        int affectedRowNum = preparedStatement_actor.executeUpdate();
        int idOfAddedActor = 0;
        ResultSet resultSet = preparedStatement_actor.getGeneratedKeys();//获得生成的主键集合
        if (resultSet.next()) {
            idOfAddedActor = resultSet.getInt(1);//读取第一个主键
        }
        //本方法是事务的一部分，不能释放资源
        JdbcHelper.close(resultSet,preparedStatement_actor);
        //新增元组的id
        return idOfAddedActor;

    }
    //删除
    public boolean delete(Actor actor) throws SQLException {
        return actors.remove(actor);
    }
    /**
     * 更新
     * @param connection
     * @param actor
     * @return
     * @throws SQLException
     */

    public boolean update(Connection connection ,Actor actor) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("UPDATE Actor SET name=?,no=? WHERE id=?");
        preparedStatement.setString(1,actor.getName());
        preparedStatement.setString(2,actor.getNo());
        preparedStatement.setInt(3,actor.getId());
        int rowAffected = preparedStatement.executeUpdate();
        JdbcHelper.close(null,preparedStatement);
        return rowAffected>0;
    }


    public Collection<Actor> findAll() throws SQLException {
        return ActorDao.actors;
    }
    /**
     * 根据页码查询
     * @param connection
     * @param conditionList
     * @param pagination
     * @return
     * @throws SQLException
     */
    public Collection<Actor> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException {
        //创建Teacher类型集合类对象，HashSet不保证结果中元素的顺序
        Collection<Actor> actors = new HashSet<Actor>();
        //创建查询教师的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM actor ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList.toString());
            sql_select.append(whereClause);
        }
        //获取actor中的元组总数
        int totalNum = CommonDao.getInstance().count(connection, "Teacher");
        if (pagination != null) {
            String paginationClause = pagination.toLimitClause(totalNum);
            sql_select.append(paginationClause);
        }
        //获得查询actor的语句对象
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        //获得actor对象的结果集
        ResultSet resultSet_select = pstmt_select.executeQuery();
        //遍历结果集，根据每个元素创建对象，并添加到集合类对象desireactors中
        while (resultSet_select.next()) {
            int id = resultSet_select.getInt("id");
            String name = resultSet_select.getString("name");
            String no = resultSet_select.getString("no");
            Actor actor = new Actor(name, no) {
            };
            actor.setId(id);
            actors.add(actor);
        }
        //关闭ResultSet, statement资源
        JdbcHelper.close(resultSet_select, pstmt_select);
        return actors;
    }

    public Actor find(Integer id, Connection connection) throws SQLException {
        //先后在Sudent和Teacher中查找相应的id
        Actor desiredActor = null;
        //查询id对应的Student对象
        desiredActor = StudentService.getInstance().find(id);
        if (desiredActor != null) {
            return desiredActor;
        }
        //查询id对应的Teacher对象
        desiredActor = TeacherService.getInstance().find(id);
        return desiredActor;
        //在各子类对象中均未查询到
    }

}
